<?php
/**
 * GioCMS
 * 
 * LICENSE
 *
 * This source file is subject to the GNU GENERAL PUBLIC LICENSE Version 2 
 * that is bundled with this package in the file LICENSE.txt.
 * It is also available through the world-wide-web at this URL:
 * http://www.gnu.org/licenses/gpl-2.0.txt
 * If you did not receive a copy of the license and are unable to
 * obtain it through the world-wide-web, please send an email
 * to license@ninhgio.com so we can send you a copy immediately.
 * 
 * @copyright	Copyright (c) 2010-2011 GioCMS (http://cms.ninhgio.com)
 * @license		http://www.gnu.org/licenses/gpl-2.0.txt GNU GENERAL PUBLIC LICENSE Version 2
 * @author		NinhGio - ninhgio@gmail.com
 * @since		1.0
 */

class Modules_Comment_Models_Mysql_Comment extends Modules_Comment_Models_Comment
{
	protected $_table = 'comment';
	
	public function getById($id) 
	{
		$sql = sprintf("SELECT * FROM " . $this->_conn->_tablePrefix . $this->_table . " 
						WHERE comment_id = '%s'
						LIMIT 1", 
						$this->_conn->escape($id));
		$rs  = $this->_conn->query($sql);
		$return = (0 == $this->_conn->count($rs)) ? null : $this->_conn->fetchAll($rs);
		$this->_conn->freeResult($rs);
		return $return;
	}
	
	public function add($comment) 
	{
		return $this->_conn->insert($comment, $this->_conn->_tablePrefix . $this->_table);					
	}
	
	public function update($comment) 
	{
		$sql = sprintf("UPDATE " . $this->_conn->_tablePrefix . $this->_table . "
						SET title = '%s', content = '%s', status = '%s', 
							email = '%s', ip = '%s', full_name = '%s', web_site = '%s', activate_date = '%s'
						WHERE comment_id = '%s'",
						$this->_conn->escape($comment['title']),
						$this->_conn->escape($comment['content']),
						$this->_conn->escape($comment['status']),
						$this->_conn->escape($comment['email']),
						$this->_conn->escape($comment['ip']),
						$this->_conn->escape($comment['full_name']),
						$this->_conn->escape($comment['web_site']),
						$this->_conn->escape($comment['activate_date']),
						$this->_conn->escape($comment['comment_id']));
		$this->_conn->query($sql);
		return $this->_conn->affectedRows();
	}
	
	public function reupdateOrderInThread($comment)
	{
		$sql = sprintf("SELECT MAX(c.ordering) AS max_ordering 
						FROM " . $this->_conn->_tablePrefix . $this->_table . " AS c
						WHERE c.params = '%s'
						LIMIT 1",
						$this->_conn->escape($comment['params']));
		$rs  = $this->_conn->query($sql);
		$row = $this->_conn->fetchAll($rs);
		$ordering = $row['max_ordering'];
		$this->_conn->freeResult($rs);
		
		$depth = 0;
		$path = $comment['comment_id'] . '-';
		if ($comment['reply_to']) {
			$replyTo = $this->getById($comment['reply_to']); 
			if ($replyTo != null) {
				$sql = sprintf("SELECT MAX(c.ordering) AS max_ordering 
								FROM " . $this->_conn->_tablePrefix . $this->_table . " AS c
								WHERE c.path LIKE '%s%'
								LIMIT 1",
								$this->_conn->escape($replyTo['path']));
				$rs  = $this->_conn->query($sql);
				if (0 == $this->_conn->count($rs)) {
					$ordering = $replyTo['ordering'];
				} else {
					$row = $this->_conn->fetchAll($rs);
					$ordering = $row['max_ordering'];
				}
				$this->_conn->freeResult($rs);
				
				$path  = $replyTo['path'] . $path;
				$depth = $replyTo['depth'] + 1;
				$sql   = sprintf("UPDATE " . $this->_conn->_tablePrefix . $this->_table . " 
								SET ordering = ordering + 1
								WHERE params = '%s' AND ordering > %s",
								$this->_conn->escape($comment['params']),
								$this->_conn->escape($ordering));
				$this->_conn->query($sql);
			}
		}		
		
		$sql = sprintf("UPDATE " . $this->_conn->_tablePrefix . $this->_table . "
						SET ordering = '%s', depth = '%s', path = '%s'
						WHERE comment_id = '%s'",
						$this->_conn->escape($ordering + 1),
						$this->_conn->escape($depth),
						$this->_conn->escape($path),
						$this->_conn->escape($comment['comment_id']));
		$this->_conn->query($sql);
		return $this->_conn->affectedRows();
	}
	
	public function delete($id) 
	{
		$sql = sprintf("DELETE FROM " . $this->_conn->_tablePrefix . $this->_table . "
						WHERE comment_id = '%s'", 
						$this->_conn->escape($id));
		$this->_conn->query($sql);
		return $this->_conn->affectedRows();
	}
	
	public function toggleActive($comment)
	{
		$sql = sprintf("UPDATE " . $this->_conn->_tablePrefix . $this->_table . "
						SET is_active = 1 - is_active, activate_date = '%s' 
						WHERE comment_id = '%s'",
						$this->_conn->escape($comment->activate_date),
						$this->_conn->escape($comment->comment_id));
		$this->_conn->query($sql);
		return $this->_conn->affectedRows();
	}
	
	public function getLatest($offset, $count, $isActive = null)
	{
		$sql = "SELECT * FROM " . $this->_conn->_tablePrefix . $this->_table . " AS c";
		if ($isActive) {
			$sql .= sprintf(" WHERE c.status = '%s'", $this->_conn->escape($isActive));
		}
		$sql .= " ORDER BY c.activate_date DESC";
		if (is_int($offset) && is_int($count)) {
			$sql .= sprintf(" LIMIT %s, %s", $offset, $count);
		}
		
		$rs   = $this->_conn->query($sql);
		$rows = array();
		while ($row = $this->_conn->fetchAll($rs)) {
			$rows[] = $row;
		}
		$this->_conn->freeResult($rs);
		return $rows;
	}
	
	public function getLatestByThread()
	{
		$sql  = "SELECT c.* FROM " . $this->_conn->_tablePrefix . $this->_table . " AS c 
				WHERE c.comment_id IN (SELECT MAX(c2.comment_id) FROM " . $this->_conn->_tablePrefix . $this->_table . " AS c2 
										GROUP BY c2.params)
				ORDER BY c.comment_id DESC";
		
		$rs   = $this->_conn->query($sql);
		$rows = array();
		while ($row = $this->_conn->fetchAll($rs)) {
			$rows[] = $row;
		}
		$this->_conn->freeResult($rs);
		return $rows;
	}

	public function countThreads()
	{
		$sql = "SELECT COUNT(DISTINCT params) AS num_threads FROM " . $this->_conn->_tablePrefix . $this->_table;
		$rs  = $this->_conn->query($sql);
		$row = $this->_conn->fetchAll($rs);
		$this->_conn->freeResult($rs);
		return $row['num_threads'];
	}
	
	public function getThreadComments($offset, $count, $params, $isActive = null)
	{
		$sql = sprintf("SELECT * FROM " . $this->_conn->_tablePrefix . $this->_table . " AS c
						WHERE c.params = '%s'",
						$this->_conn->escape($params));
		if ($isActive) {
			$sql .= sprintf(" AND c.status = '%s'", $this->_conn->escape($isActive));
		}
		$sql .= " ORDER BY created_date DESC";
		if (is_int($offset) && is_int($count)) {
			$sql .= sprintf(" LIMIT %s, %s", $offset, $count);
		}
		
		$rs   = $this->_conn->query($sql);
		$rows = array();
		while ($row = $this->_conn->fetchAll($rs)) {
			$rows[] = $row;
		}
		$this->_conn->freeResult($rs);
		return $rows;
	}
	
	public function countThreadComments($params, $isActive = null)
	{
		$sql = sprintf("SELECT COUNT(*) AS num_comments FROM " . $this->_conn->_tablePrefix . $this->_table . " AS c
						WHERE c.params = '%s'",
						$this->_conn->escape($params));
		if ($isActive) {
			$sql .= sprintf(" AND c.status = '%s'", $this->_conn->escape($isActive));
		}
		$rs  = $this->_conn->query($sql);
		$row = $this->_conn->fetchAll($rs);
		$this->_conn->freeResult($rs);
		return $row['num_comments'];
	}
	
	public function find($offset = null, $limit = null, $condition = array())
	{
		$sql = "SELECT * FROM " . $this->_conn->_tablePrefix . $this->_table . " AS c";
		$sql .= " ORDER BY comment_id DESC";
		if (is_int($offset) && is_int($limit)) {
			$sql .= sprintf(" LIMIT %s, %s", $offset, $limit);
		}
		
		$rs   = $this->_conn->query($sql);
		$rows = array();
		while ($row = $this->_conn->fetchAll($rs)) {
			$rows[] = $row;
		}
		$this->_conn->freeResult($rs);
		return $rows;
	}
	
	public function count($condition = array())
	{
		$sql = "SELECT COUNT(*) AS num_comments FROM " . $this->_conn->_tablePrefix . $this->_table . " AS c";
		$rs  = $this->_conn->query($sql);
		$row = $this->_conn->fetchAll($rs);
		$this->_conn->freeResult($rs);
		return $row['num_comments'];
	}
}
